package cn.bdqn.dao.impl;

import cn.bdqn.dao.AirDao;
import cn.bdqn.entity.Air;
import cn.bdqn.util.DataSourceUtil;
import org.apache.commons.dbutils.QueryRunner;
<<<<<<< HEAD
=======
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
>>>>>>> 8401facf80807854843aea47729c77b70fec9b79

import java.sql.SQLException;
import java.util.List;

<<<<<<< HEAD
/**
 * 空气质量指数的dao/层实现类
 * Created by Lenovo on 2018/10/9.
 */
public class AirDaoImpl implements AirDao {
    private QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
    //保存
    @Override
    public Integer save(Air air) throws SQLException {
        String sql = "INSERT INTO \n" +
                "\tair\n" +
                "(districtId,montitorTime,pm10,pm25, monitoringStation,lastModifyTime)\n" +
                "VALUES\n" +
                "(?,?,?,?,?,?);";
        Object[] args = {air.getDistrictId(),air.getMontitorTime(),air.getPm10(),air.getPm25(),air.getMonitoringStation(),air.getLastModifyTime()};
        int count = qr.update(sql, args);
        return count;
    }
    //删除
    @Override
    public Integer daleteByIda(Integer id) {
        String sql = "DELETE FROM air WHERE id = ?";
        return null;
    }
    //修改
    @Override
    public Integer update(Air air) {
        return null;
    }

    @Override
    public Air findById(Integer id) {
        return null;
    }

    @Override
    public List<Air> findByCondition(Integer distructId, Integer startInder, Integer pageSize) {
        return null;
=======

public class AirDaoImpl implements AirDao {

    private QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());

    // 保存
    @Override
    public Integer save(Air air) throws SQLException {
        String sql = "insert into \n" +
                "  air\n" +
                "(districtid,monitortime,pm10,pm25,monitoringstation,lastmodifytime)\n" +
                "  values\n" +
                "(?,?,?,?,?,?)";
        Object[] args = {air.getDistrictId(),air.getMonitorTime(),air.getPm10(),air.getPm25(),air.getMonitoringStation(),air.getLastModifyTime()};
        int count = qr.update(sql, args);
        return count;
    }

    //删除
    @Override
    public Integer deleteById(Integer id) throws SQLException {
        String sql = "delete from \n" +
                "  air \n" +
                "where \n" +
                "  id = ?";
        int count = qr.update(sql, id);
        return count;
    }


    //修改
    @Override
    public Integer update(Air air) throws SQLException {
        String sql = "update \n" +
                "  air \n" +
                "set \n" +
                "  districtid = ?,\n" +
                "  monitortime = ?,\n" +
                "  pm10 = ?,\n" +
                "  pm25 = ?,\n" +
                "  monitoringstation = ?,\n" +
                "  lastmodifytime = ?\n" +
                "where \n" +
                "  id = ?;";
        Object[] args = {air.getDistrictId(),air.getMonitorTime(),air.getPm10(),air.getPm25(),air.getMonitoringStation(),air.getLastModifyTime(),air.getId()};
        int count = qr.update(sql, args);
        return count;
    }


    //根据id查询
    @Override
    public Air findById(Integer id) throws SQLException {
        String sql = "select * from air where id = ?";
        Air air = qr.query(sql, new BeanHandler<Air>(Air.class), id);
        return air;
    }

    //根据三个条件查询
    @Override
    public List<Air> findByCondition(Integer distructId, Integer startIndex, Integer pageSize) throws SQLException {
        String sql = "select \n" +
                "  a.* ,\n" +
                "  d.name dname\n" +
                "from \n" +
                "  air a,district d\n" +
                "where \n" +
                "  a.districtid = d.id ";
        if(distructId != null && distructId > 0){
            //SQL中有条件..
            sql += "and a.districtid = ? \n" +
                    "order by lastmodifytime \n" +
                    "limit ?,?";
            List<Air> list = qr.query(sql, new BeanListHandler<Air>(Air.class), distructId, startIndex, pageSize);
            return list;
        }else{
            //sql没有条件..
            sql += "order by lastmodifytime \n" +
                    "limit ?,?";
            List<Air> list = qr.query(sql, new BeanListHandler<Air>(Air.class), startIndex, pageSize);
            return list;
        }
    }

    @Override
    public Integer findCountByCondition(Integer districtId) throws SQLException {
        String sql = "select \n" +
                "  count(1)\n" +
                "from \n" +
                "  air a,district d\n" +
                "where \n" +
                "  a.districtid = d.id ";
        if(districtId != null && districtId > 0){
            sql += " and a.districtid = ?";
            Long count = qr.query(sql, new ScalarHandler<Long>(), districtId);
            return Integer.valueOf(count + "");
        }else{
            Long count = qr.query(sql, new ScalarHandler<Long>());
            return Integer.valueOf(count + "");
        }
>>>>>>> 8401facf80807854843aea47729c77b70fec9b79
    }
}
